
#include <stdio.h>
#include <string.h>
#include <sqlite3.h>

#define DB_NAME "test.sql"
#define STR_LEN 256     // sqlite varchar(255) ...

struct dept
{
   short deptno;
   char dname[STR_LEN];     
   char location[STR_LEN];
};

void sql_exec(sqlite3 *db, char *query)
{

    char *errmsg = 0;
	int rc;
	if(db != 0)
	{
		rc = sqlite3_exec(db, query, 0, 0, &errmsg );		
		if(rc!=SQLITE_OK)
		{
			fprintf(stderr, "[!] SQL error: %s\n", errmsg);
			sqlite3_free(errmsg);
		}
	}
	else
	{
		fprintf(stderr, "sqlite3 *db = 0 error\n");
	}

}


void print_dept(struct dept *p)
{
    printf("%d\t%s\t%s\n",
        p->deptno,
        p->dname,
        p->location
        );
}


short db_name_exists(char* db_name)
{
	FILE *fp = fopen(db_name, "r");
	short exist = 0;
	if(fp)
	{
		exist = 1;
		fclose(fp);
	}	
	return exist;
}

// test function ...
void init_dept(sqlite3 *db, struct dept *p)
{
    sqlite3_stmt *stmt;
    sqlite3_prepare(db, "SELECT * FROM dept where deptno=10", -1, &stmt, 0);

    while(sqlite3_step(stmt) != SQLITE_DONE)
    {
           memset(&p->dname, 0, STR_LEN);
           memset(&p->location, 0, STR_LEN);
           p->deptno = sqlite3_column_int(stmt, 0);
           strncpy((char *)&p->dname, sqlite3_column_text(stmt, 1), strlen(sqlite3_column_text(stmt, 1)));
           strncpy((char *)&p->location, sqlite3_column_text(stmt, 2), strlen(sqlite3_column_text(stmt, 2)));
    }

    sqlite3_finalize(stmt);
}

int main()
{
	sqlite3* db = 0;
	short db_exists = db_name_exists(DB_NAME);

	if(sqlite3_open(DB_NAME, &db) )
	{
		fprintf(stderr, "[!] Can't open database: %s\n", sqlite3_errmsg(db));	
        sqlite3_close(db);
	}

	if(!db_exists) 
    {
        //sql_exec(db, "drop table dept");
        sql_exec(db, "CREATE TABLE dept (deptno integer primary key, dname varchar, location varchar);");		
        sql_exec(db, "INSERT INTO dept VALUES (10, 'Sales', 'New York');");
        sql_exec(db, "INSERT INTO dept VALUES (20, 'Accounting', 'Berlin');");
    }


    struct dept t;
    init_dept(db, &t);
    print_dept(&t);
   

	sqlite3_close(db);

	return 0;
}

